package pub.cleangao;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.FileInputStream;
import java.text.DecimalFormat;
import java.util.ArrayList;
import java.util.List;


public class ExcelProcess {

    public static Workbook loadFromFile(String filePath) throws Exception {
        String extend = filePath.substring(filePath.lastIndexOf("."));
        if (extend.equalsIgnoreCase(".xls")) {
            return new HSSFWorkbook(new FileInputStream(filePath));
        } else if (extend.equalsIgnoreCase(".xlsx")) {
            return new XSSFWorkbook(new FileInputStream(filePath));
        }
        return null;
    }


    public static List<String> readColumnFromSheet(String filePath, int sheetIndex, int columnIndex, int skipRowNum) throws Exception {
        Workbook workbook = loadFromFile(filePath);
        int sheetCount = workbook.getNumberOfSheets();
        if (sheetIndex >= sheetCount) {
            return null;
        }
        Sheet sheet = workbook.getSheetAt(sheetIndex);
        int rowNum = sheet.getPhysicalNumberOfRows();
        if (skipRowNum >= rowNum) {
            return null;
        }
        System.out.println("文件:" + filePath + " 第 " + (sheetIndex + 1) + "个表，总共 " + rowNum + " 行");
        List<String> rst = new ArrayList<>(rowNum);
        for (int i = skipRowNum; i < rowNum; i++) {
            Row row = sheet.getRow(i);
            if (row == null) {
                continue;
            }
            Cell cell = row.getCell(columnIndex);
            if (cell == null) {
                continue;
            }
            rst.add(convertCellValueToString(cell));

        }
        return rst;
    }

    private static String convertCellValueToString(Cell cell) {
        if (cell == null) {
            return null;
        }
        String returnValue = null;
        switch (cell.getCellType()) {
            case NUMERIC:   //数字
                Double doubleValue = cell.getNumericCellValue();

                // 格式化科学计数法，取一位整数
                DecimalFormat df = new DecimalFormat("0");
                returnValue = df.format(doubleValue);
                break;
            case STRING:    //字符串
                returnValue = cell.getStringCellValue();
                break;
            case BOOLEAN:   //布尔
                Boolean booleanValue = cell.getBooleanCellValue();
                returnValue = booleanValue.toString();
                break;
            case BLANK:     // 空值
                break;
            case FORMULA:   // 公式
                returnValue = cell.getCellFormula();
                break;
            case ERROR:     // 故障
                break;
            default:
                break;
        }
        if (returnValue == null || returnValue.trim().equals("")) {
            return null;
        }
        return returnValue.trim();
    }


}
